Working with DataColumns

The DataColumn type represents a single column within a DataTable. Collectively speaking, the set of all DataColumn types bound to a given DataTable represents the foundation of a table’s schema information. For example, if you were to model the Inventory table of the AutoLot database (see Chapter 21), you would create four DataColumns, one for each column (CarID, Make, Color, and PetName). Once you create your DataColumn objects, you typically add them into the columns collection of the DataTable type (using the Columns property).

Based on your background, you might know that you can assign a given column in a database table a set of constraints (e.g., configured as a primary key, assigned a default value, or configured to contain read-only information). Also, every column in a table must map to an underlying data type. For example, the Inventory table’s schema requires that the CarID column map to an integer, while Make, Color, and PetName map to an array of characters. The DataColumn class has numerous properties that allow you to configure precisely these things. Table 22-3 provides a rundown of some core properties.

Table 22-3. Properties of the DataColumn

Building a DataColumn

To continue with the SimpleDataSet project (and illustrate the use of the DataColumn), assume you wish to model the columns of the Inventory table. Given that the CarID column will be the table’s primary key, you will configure this DataColumn object as read-only, unique, and non-null (using the ReadOnly, Unique, and AllowDBNull properties). Next, update the Program class with a new method named FillDataSet(), which you use to build four DataColumn objects. Note this method takes a DataSet object as its only parameter:

static void FillDataSet(DataSet ds)
{
    // Create data columns that map to the
    // 'real' columns in the Inventory table
    // of the AutoLot database.
    DataColumn carIDColumn = new DataColumn("CarID", typeof(int));
    carIDColumn.Caption = "Car ID";
    carIDColumn.ReadOnly = true;
    carIDColumn.AllowDBNull = false;
    carIDColumn.Unique = true;
    
    DataColumn carMakeColumn = new DataColumn("Make", typeof(string));
    DataColumn carColorColumn = new DataColumn("Color", typeof(string));
    DataColumn carPetNameColumn = new DataColumn("PetName", typeof(string));
    carPetNameColumn.Caption = "Pet Name";
}

Notice that when you configure the carIDColumn object, you assign a value to the Caption property. This property is helpful because it allows you to define a string value for display purposes, which can be distinct from the literal database table column name (column names in a literal database table are typically better suited for programming purposes [e.g., au_fname] than display purposes [e.g., Author First Name]). Here, you set the caption for the PetName column for the same reason, because Pet Name looks nicer than PetName to the end user.

Enabling Autoincrementing Fields

One aspect of the DataColumn you can choose to configure is its ability to autoincrement. You use an autoincrementing column to ensure that when a new row is added to a given table, the value of this column is assigned automatically, based on the current step of the increase. This can be helpful when you wish to ensure that a column has no repeating values (e.g., a primary key).

You control this behavior using the AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties. You use the seed value to mark the starting value of the column; you use the step value to identify the number to add to the seed when incrementing. Consider the following update to the construction of the carIDColumn DataColumn:

static void FillDataSet(DataSet ds)
{
    DataColumn carIDColumn = new DataColumn("CarID", typeof(int));
    carIDColumn.ReadOnly = true;
    carIDColumn.Caption = "Car ID";
    carIDColumn.AllowDBNull = false;
    carIDColumn.Unique = true;
    carIDColumn.AutoIncrement = true;
    carIDColumn.AutoIncrementSeed = 0;
    carIDColumn.AutoIncrementStep = 1;
...
}

Here, you configure the carIDColumn object to ensure that, as rows are added to the respective table, the value for this column is incremented by 1. You set the seed at 0, so this column would be numbered 0, 1, 2, 3, and so forth.

Adding DataColumn Objects to a DataTable

The DataColumn type does not typically exist as a stand-alone entity; however, you do typically insert it into a related DataTable. For example, create a new DataTable object (fully detailed in a moment) and insert each DataColumn object in the columns collection using the Columns property:

static void FillDataSet(DataSet ds):
{
...
    // Now add DataColumns to a DataTable.
    DataTable inventoryTable = new DataTable("Inventory");
    inventoryTable.Columns.AddRange(new DataColumn[]
        { carIDColumn, carMakeColumn, carColorColumn, carPetNameColumn });
}

At this point, the DataTable object contains four DataColumn objects that represent the schema of the in-memory Inventory table. However, the table is currently devoid of data, and the table is currently outside of the table collection maintained by the DataSet. You will deal with both of these shortcomings, beginning by populating the table with data using a DataRow objects.